package com.gem.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import com.gem.dao.UserDAO;
import com.gem.pojo.Users;
import com.gem.util.DBHelper;

public class UserDAOImpl implements UserDAO {
	private static Logger log=Logger.getLogger(UserDAOImpl.class.getName());
	Connection conn=null;
	Statement sta=null;
	PreparedStatement  psta=null;
	ResultSet res=null;
	@Override
	public boolean addUser(Users user) {
		// TODO Auto-generated method stub
		try{
		conn=DBHelper.getConnection();
		sta=conn.createStatement();
		String sql="insert into users values('"+user.getUid()+"','"+user.getUname()+"','"+user.getUpassword()+"',"+user.getUsex()+",'"+user.getuPhone()+"','"+user.getUaddress()+"',1,"+user.getUmoney()+")";
		int num=sta.executeUpdate(sql);			//����ֵΪ��Ӱ�������
		if(num!=0){
			return true;
		}
		}catch(SQLException ex){
			log.error(ex);
		}finally{
			DBHelper.closeAll(conn, null, res);
		}
		return false;
	}

	@Override
	public Users getUser(Users user) {
		// TODO Auto-generated method stub
		Users u=null;
		conn=DBHelper.getConnection();
		try{
			String sql="select uid,upassword from users where uid=? and upassword=?";
			psta=conn.prepareStatement(sql);
			psta.setString(1, user.getUid());
			psta.setString(2, user.getUpassword());
			res=psta.executeQuery();
			if(res.next()){
				u=new Users();
				u.setUid(res.getString(1));
				u.setUpassword(res.getString(2));
			}
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, psta, res);
			}
		return u;
	}

	@Override
	public List<Users> getAllUsers() {
		// TODO Auto-generated method stub
		Users u=null;
		List<Users> listusers=new ArrayList<Users>();
		try{
			conn=DBHelper.getConnection();
			sta=conn.createStatement();
			String sql="select uid,uname,usex,uphone,uaddress,utype,umoney from users";
			res=sta.executeQuery(sql);
			while(res.next()){
				u=new Users();
				u.setUid(res.getString(1));
				u.setUname(res.getString(2));
				u.setUsex(res.getInt(3));
				u.setuPhone(res.getString(4));
				u.setUaddress(res.getString(5));
				u.setUtype(res.getInt(6));
				u.setUmoney(res.getDouble(7));
				listusers.add(u);
			}
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, null, res);
			}
		return listusers;
	}

	@Override
	public Users getUserInfo(String uid) {
		// TODO Auto-generated method stub
		Users u=null;
		try{
			conn=DBHelper.getConnection();
			String sql="select * from users where uid=?";
			psta=conn.prepareStatement(sql);
			psta.setString(1, uid);
			res=psta.executeQuery();
			while(res.next()){
				u=new Users();
				u.setUid(res.getString(1));
				u.setUpassword(res.getString(3));
			}
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, psta, res);
			}
		return u;
	}

	@Override
	public boolean updateUserInfo(Users user) {
		// TODO Auto-generated method stub
		try{
			conn=DBHelper.getConnection();
			String sql="update users set uname=?,upassword=?,usex=?,uphone=?,uaddress=?,utype=?,umoney=? where uid=?";
			psta=conn.prepareStatement(sql);
			psta.setString(1, user.getUname());
			psta.setString(2, user.getUpassword());
			psta.setInt(3, user.getUsex());
			psta.setString(4, user.getuPhone());
			psta.setString(5, user.getUaddress());
			psta.setInt(6, user.getUtype());
			psta.setDouble(7, user.getUmoney());
			psta.setString(8, user.getUid());
			psta.executeUpdate();
			return true;
		}catch(SQLException ex){
			log.error(ex);
		}finally{
			DBHelper.closeAll(conn, psta, res);
		}
		return false;
	}

	@Override
	public boolean delUser(String uid) {
		// TODO Auto-generated method stub
		try{
				conn=DBHelper.getConnection();
				String sql="delete from users where uid=?";
				psta=conn.prepareStatement(sql);
				psta.setString(1, uid);
				psta.executeUpdate();
				return true;
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, psta, res);
			}
		return false;
	}
	/*public static void main(String[] args) {
		Users user=new Users();
		user.setUid("000001");
		user.setUpassword("678678");
		UserDAOImpl ud=new UserDAOImpl();
		Users result=ud.getUser(user);
		System.out.println(result.getUpassword());
		//Users result=ud.getUserInfo("000001");
		System.out.println(result.getUpassword());
	}*/

	@Override
	public List<Users> getAllUsersByPage(int page_user_num, int page_num) {
		// TODO Auto-generated method stub
		Users u=null;
		List<Users> listusers=new ArrayList<Users>();
		try{
			conn=DBHelper.getConnection();
			sta=conn.createStatement();
			String sql = "select top " + page_user_num +" *"
					+ " from users "
					+ " where uid not in"
					+ " (select top " + ((page_num-1) * page_user_num)
						+ " uid from users " 
						+ "order by uid desc)"
					+ " order by uid desc";
			res=sta.executeQuery(sql);
			while(res.next()){
				u=new Users();
				u.setUid(res.getString(1));
				u.setUname(res.getString(2));
				u.setUsex(res.getInt(4));
				u.setuPhone(res.getString(5));
				u.setUaddress(res.getString(6));
				u.setUtype(res.getInt(7));
				u.setUmoney(res.getDouble(8));
				listusers.add(u);
			}
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, null, res);
			}
		return listusers;
	}

	@Override
	public int getUsersAcount() {
		// TODO Auto-generated method stub
		conn=DBHelper.getConnection();
		int num=0;
		try{
			String sql="select count(*)from users";
			psta=conn.prepareStatement(sql);
			res=psta.executeQuery();
			if(res.next()){
				num=res.getInt(1);
			}
			}catch(SQLException ex){
				log.error(ex);
			}finally{
				DBHelper.closeAll(conn, psta, res);
			}
		return num;
	}
}
